<?php
/**************************************************************************
	ESAM (EVE to SMF API Module)
	Copyright (C) 2009  Clinton Weiss

	This program is free software: you can redistribute it and/or modify
	it under the terms of the GNU General Public License as published by
	the Free Software Foundation, either version 3 of the License, or
	(at your option) any later version.

	This program is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
	GNU General Public License for more details.

	You should have received a copy of the GNU General Public License
	along with this program.  If not, see <http://www.gnu.org/licenses/>.
**************************************************************************/

/**
 * class for executing queries
 */
class EsamQuery
{
	// API
	public $Q_EVE_API					= "SELECT * FROM {db_prefix}esam_api WHERE ID_MEMBER = {member_id}";
	public $Q_EVE_API_LIST		= "SELECT * FROM {db_prefix}esam_api ORDER BY ID_MEMBER";
	public $Q_EVE_API_SEARCH	= "SELECT * FROM {db_prefix}esam_api WHERE USER_ID = '{user_id}' AND API_KEY = '{api_key}'";
	public $D_EVE_API_DELETE	= "DELETE FROM {db_prefix}esam_api	WHERE ID_MEMBER = {member_id}";
	public $D_CLEAR_APIKEY		= "UPDATE {db_prefix}esam_api SET API_KEY = '' WHERE id_member = {member_id}";
	public $I_EVE_API_INSERT	= "INSERT INTO {db_prefix}esam_api (ID_MEMBER, USER_ID, API_KEY) VALUES ({member_id}, '{user_id}', '{api_key}')";

	// Rules
	public $Q_RULES						= "SELECT * FROM {db_prefix}esam_rules order by type, role, role_id, group_id";
	public $D_RULE_DELETE			= "DELETE FROM {db_prefix}esam_rules WHERE ID_RULE = {id_rule}";
	public $I_RULE_INSERT			= "INSERT INTO {db_prefix}esam_rules (TYPE, ROLE, ROLE_ID, GROUP_ID, SKILL_ID, SKILL_LVL) VALUES ('{type}', '{role}', {role_id}, {group_id}, {skill_type_id}, {skill_lvl})";

	// Settings
	public $Q_MEMBER_SETTING	= "SELECT * FROM {db_prefix}esam_settings WHERE NAME = '{name}' AND ID_MEMBER = {member_id}";
	public $I_MEMBER_SETTING	= "INSERT INTO {db_prefix}esam_settings (name, value, id_member) VALUES ('{name}', '{value}', {member_id})";
	public $D_MEMBER_SETTING	= "DELETE FROM {db_prefix}esam_settings WHERE NAME = '{name}' AND ID_MEMBER = {member_id}";

	// Member Groups
	public $Q_MGROUP_LIST			= "select * from {db_prefix}membergroups where minPosts = -1 and id_group not in (-1, 0, 1, 3);";
	public $Q_MGROUP_INFO			= "SELECT * FROM {db_prefix}membergroups WHERE ID_GROUP = {id_group}";
	public $D_MGROUP_CLEAR		= "UPDATE {db_prefix}members SET additionalGroups = '' WHERE ID_MEMBER = {member_id}";
	public $U_MGROUP_ADD			= "
		UPDATE {db_prefix}members
		SET
			additionalGroups = IF(additionalGroups = '', '{id_group}', CONCAT(additionalGroups, ',{id_group}'))
		WHERE ID_MEMBER = {member_id}
			AND ID_GROUP != {id_group}
			AND NOT FIND_IN_SET({id_group}, additionalGroups)";

	// Member
	public $Q_MEMBER_INFO			= "SELECT * FROM {db_prefix}members WHERE ID_MEMBER = {member_id}";
	public $U_MEMBER_NAME			= "UPDATE {db_prefix}members set realName = '{real_name}' WHERE ID_MEMBER = {member_id}";
	public $U_MEMBERS_GROUPS	= "UPDATE {db_prefix}members set additionalGroups = '{member_groups}' WHERE ID_MEMBER = {member_id}";

	private $query = null;

	/**
	 * Validates that the parameter is not an empty value, if it is,
	 * calls fatal_error() with the given error message.
	 *
	 * @param <type> $parameter
	 * @param <type> $errorMessage
	 */
	public function validate($parameter, $errorMessage)
	{
		if (empty($parameter))
		{
			fatal_error($errorMessage, false);
		}
	}

	/**
	 * Sets the executed query.
	 *
	 * @global <type> $db_prefix
	 * @param <type> $_query
	 */
	public function setQuery($_query)
	{
		global $db_prefix;

		$this->query = $_query;

		$this->setParameter("db_prefix", "{$db_prefix}");
	}

	/**
	 * Sets the parameter name to the given value.
	 *
	 * NOTE: It is up to the caller to quote strings.
	 *
	 * @param <type> $name
	 * @param <type> $value
	 */
	public function setParameter($name, $value)
	{
		$this->validate($this->query, "Must call setQuery() first.");
		$this->validate($name, "name cannot be empty.");

		if (empty($value) || $value == null)
		{
			$value = "null";
		}

		$this->query = str_replace("{" . $name . "}", $value, $this->query);
	}

	public function executeQuery()
	{
		// print "<pre>" . $this->query . "</pre>";
		db_query($this->query, __FILE__, __LINE__);
	}

	/**
	 * Executes the query and returns the result set as an array()
	 *
	 * @return <type>
	 */
	public function getResultSet()
	{
		$request = db_query($this->query, __FILE__, __LINE__);
    $result = array();
    while($row = mysql_fetch_assoc($request))
		{
       $result[] = $row;
    }
		mysql_free_result($request);
    return $result;
	}

	/**
	 * Executes the query and returns the the first value from
	 * the given column.
	 *
	 * @return <type>
	 */
	public function getResultSetValue($column)
	{
		$request = db_query($this->query, __FILE__, __LINE__);
    $row = mysql_fetch_assoc($request);
		mysql_free_result($request);
    return $row[$column];
	}
}
?>